set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_tmp.dm_route_whole_shift_base_dt_mid
select 
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum
    ,branch_in_total_all_time
    ,main_total_all_time
    ,branch_out_total_all_time
    ,lasted_line_name
    ,if_city
from (
    select *,row_number() over(partition by in_from_code,out_to_code,latest_warehousing_time_quantum,e1_line_code order by search_type,total_time_use,num_of_transfer asc,branch_in_center_stop_time asc) as rk --取中转次数最少
    from (
        select
              route.*
             ,coalesce(e6_line_code,e5_line_code,e4_line_code,e3_line_code,e2_line_code,e1_line_code) as lasted_line_name --中转环节最后一个车线名称
             ,case when in_from_provider_code = out_to_provider_code --始发省=目的省
                     or (in_to_code is not null and  coalesce(e1_end_code,e2_end_code,e3_end_code,e4_end_code,e5_end_code,e6_end_code) is null) --经过一个中心
                     or in_to_code is null then 1 --没有经过中心
                   else 0 end as if_city --同城件
        from jms_dm.dm_route_whole_base_dt route --全量路由
        where route.dt = '{{ execution_date | cst_ds }}'
          and route.is_main_route <> 0 --剔除备用路由
          and route.in_from_name is not null
          and route.out_to_code is not null --剔除半截路由
    ) whole
) whole where rk = 1
distribute by pmod(hash(rand()), 100);



insert overwrite  table jms_tmp.dm_route_whole_shift_base_dt_mid_2
select 
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum
    ,branch_in_total_all_time
    ,main_total_all_time
    ,branch_out_total_all_time
    ,lasted_line_name
    ,if_city
from (
    select *
        ,row_number() over(partition by in_from_code,out_to_code,latest_warehousing_time_quantum order by search_type,total_time_use,num_of_transfer asc,branch_in_center_stop_time asc,in_to_code asc) as rk
    from jms_tmp.dm_route_whole_shift_base_dt_mid
) a where a.rk = 1
distribute by pmod(hash(rand()), 100);
    
    

with tmp as (
    select * from jms_tmp.dm_route_whole_shift_base_dt_mid_2    
),

tmp_2 as (
    select 
         in_from_code
        ,out_to_code
        ,count(distinct latest_warehousing_time_quantum) as first_taking_cnt
    from jms_tmp.dm_route_whole_shift_base_dt_mid
    group by in_from_code
            ,out_to_code
),

tmp_3 as (
    select
         nvl(base.in_from_code,shift.in_from_code) as in_from_code
        ,shift.update_date
        ,shift.in_from_provider_code
        ,shift.in_from_city_code
        ,shift.in_from_area_code
        ,shift.out_to_provider_code
        ,shift.out_to_city_code
        ,shift.out_to_area_code
        ,nvl(base.out_to_code,shift.out_to_code) as out_to_code
        ,shift.in_branch_id
        ,shift.main_id
        ,shift.out_branch_id
        ,shift.in_collect_code
        ,shift.in_to_code
        ,shift.start_center_code
        ,shift.search_type
        ,shift.start_network_code
        ,shift.in_from_regional_code
        ,shift.in_from_regional_desc
        ,shift.in_from_financial_center_code
        ,shift.in_from_financial_center_desc
        ,shift.in_from_provider_desc
        ,shift.in_from_city_desc
        ,shift.in_from_area_desc
        ,shift.in_collect_name
        ,shift.in_from_name
        ,shift.in_to_name
        ,shift.in_edge_latest_warehousing
        ,shift.in_edge_planned_departure
        ,shift.in_edge_planned_arrival
        ,shift.in_edge2_planned_departure
        ,shift.in_edge2_planned_arrival
        ,shift.branch_in_span_days
        ,shift.branch_in_total_time
        ,shift.is_main_route
        ,shift.transfer_type
        ,shift.num_of_transfer
        ,shift.whole_route
        ,shift.e1_center_flow
        ,shift.e1_line_code
        ,shift.e1_planned_departure
        ,shift.e1_runtime
        ,shift.e1_planned_arrival
        ,shift.e1_e2_stop_time
        ,shift.e2_center_flow
        ,shift.e2_line_code
        ,shift.e2_planned_departure
        ,shift.e2_runtime
        ,shift.e2_planned_arrival
        ,shift.e2_e3_stop_time
        ,shift.e3_center_flow
        ,shift.e3_line_code
        ,shift.e3_planned_departure
        ,shift.e3_runtime
        ,shift.e3_planned_arrival
        ,shift.e3_e4_stop_time
        ,shift.e4_center_flow
        ,shift.e4_line_code
        ,shift.e4_planned_departure
        ,shift.e4_runtime
        ,shift.e4_planned_arrival
        ,shift.e4_e5_stop_time
        ,shift.e5_center_flow
        ,shift.e5_line_code
        ,shift.e5_planned_departure
        ,shift.e5_runtime
        ,shift.e5_planned_arrival
        ,shift.e5_e6_stop_time
        ,shift.e6_center_flow
        ,shift.e6_line_code
        ,shift.e6_planned_departure
        ,shift.e6_runtime
        ,shift.e6_planned_arrival
        ,shift.e6_e7_stop_time
        ,shift.e1_end_code
        ,shift.e2_end_code
        ,shift.e3_end_code
        ,shift.e4_end_code
        ,shift.e5_end_code
        ,shift.e6_end_code
        ,shift.transfer_total_span_days
        ,shift.transfer_total_time_use
        ,shift.main_total_span_days
        ,shift.main_total_time_use
        ,shift.end_center
        ,shift.end_center_code
        ,shift.start_center
        ,shift.out_from_code
        ,shift.out_collect_code
        ,shift.out_to_regional_desc
        ,shift.out_to_regional_code
        ,shift.out_to_financial_center_desc
        ,shift.out_to_financial_center_code
        ,shift.out_to_provider_desc
        ,shift.out_to_city_desc
        ,shift.out_to_area_desc
        ,shift.out_collect_name
        ,shift.out_to_name
        ,shift.out_from_name
        ,shift.out_has_collect
        ,shift.out_edge_planned_departure
        ,shift.out_edge_planned_arrival_time
        ,shift.out_edge2_planned_departure
        ,shift.out_edge2_planned_arrival_time
        ,shift.out_edge2_latest_warehouse_time
        ,shift.out_edge_latest_warehouse_time
        ,shift.out_edge_span_days_sign
        ,shift.out_edge2_span_days_sign
        ,shift.out_edge_deadline_sign_time
        ,shift.out_edge2_deadline_sign_time
        ,shift.branch_out_span_days
        ,shift.branch_out_total_time
        ,shift.effective_date
        ,shift.expiration_date
        ,shift.branch_in_center_stop_time
        ,shift.center_branch_out_stop_time
        ,shift.branch_in_center_span_days
        ,shift.center_branch_out_span_days
        ,shift.is_circuitous
        ,shift.out_edge_span_days_arrive
        ,shift.out_edge2_span_days_arrive
        ,shift.in_nodes
        ,shift.out_nodes
        ,shift.total_days_use
        ,shift.total_time_use
        ,shift.total_days_t
        ,shift.total_nodes
        ,shift.working_days
        ,shift.extra_in_collect_code
        ,shift.extra_in_collect_name
        ,shift.extra_in_line_name
        ,shift.extra_in_latest_warehousing
        ,shift.extra_in_planned_departure
        ,shift.extra_in_planned_arrival
        ,shift.extra_out_collect_code
        ,shift.extra_out_collect_name
        ,shift.extra_out_line_name
        ,shift.extra_out_latest_warehousing
        ,shift.extra_out_planned_departure
        ,shift.extra_out_planned_arrival
        ,shift.in_network_line_name
        ,shift.in_collect_line_name
        ,shift.out_network_line_name
        ,shift.out_collect_line_name
        ,shift.town_plus_time
        ,shift.branch_in_ship_time
        ,shift.branch_in_ship_span_days
        ,shift.branch_out_ship_time
        ,shift.branch_out_ship_span_days
        ,shift.all_line_name
        ,shift.out_town_plus_time
        ,shift.in_edge_run_time
        ,shift.in_edge_span_days
        ,shift.in_edge2_run_time
        ,shift.in_edge2_span_days
        ,shift.in_edge3_run_time
        ,shift.in_edge3_span_days
        ,shift.out_edge_run_time
        ,shift.out_edge_span_days
        ,shift.out_edge2_run_time
        ,shift.out_edge2_span_days
        ,shift.out_edge3_run_time
        ,shift.out_edge3_span_days
        ,shift.e1_span_days
        ,shift.e2_span_days
        ,shift.e3_span_days
        ,shift.e4_span_days
        ,shift.e5_span_days
        ,shift.e6_span_days
        ,shift.delivery_time
        ,shift.route_flow
        ,shift.in_edge2_latest_warehousing
        ,shift.e1_latest_warehousing
        ,shift.e2_latest_warehousing
        ,shift.e3_latest_warehousing
        ,shift.e4_latest_arrival_time
        ,shift.e5_latest_arrival_time
        ,shift.e6_latest_arrival_time
        ,shift.start_taking_shift
        ,shift.end_send_shift
        ,shift.warehouse_end_time
        ,shift.reserve_1
        ,shift.reserve_2
        ,shift.route_contain_main_line
        ,shift.direct_route_type
        ,shift.in_line_day
        ,shift.out_line_day
        ,shift.in_ship_span_days
        ,shift.in_manage_region_code
        ,shift.in_manage_region_name
        ,shift.out_manage_region_code
        ,shift.out_manage_region_name
        ,nvl(shift.latest_warehousing_time_quantum,1) as latest_warehousing_time_quantum
        ,shift.branch_in_total_all_time
        ,shift.main_total_all_time
        ,shift.branch_out_total_all_time
        ,shift.lasted_line_name
        ,shift.if_city
    from tmp base 
    left join jms_tmp.dm_route_whole_shift_base_dt_mid shift
       on base.in_from_code = shift.in_from_code 
      and base.out_to_code = shift.out_to_code
      and base.in_to_code = shift.in_to_code
      and base.latest_warehousing_time_quantum = shift.latest_warehousing_time_quantum
),


tmp_4 as (
    select 
         in_from_code
        ,out_to_code
        ,count(distinct latest_warehousing_time_quantum) as last_taking_cnt
        ,max(in_to_code) as in_to_code
    from tmp_3 
    group by in_from_code
            ,out_to_code
),


tmp_5 as (
    select 
         base.in_from_code
        ,base.out_to_code
        ,max(first_taking_cnt) as first_taking_cnt
        ,max(case when shift.in_to_code is not null then 1 else 0 end) as if_center
        ,max(shift.in_to_code) as in_to_code
    from (
        select
             last_tak.*
            ,first_tak.first_taking_cnt
        from tmp_4 as last_tak 
        left join tmp_2 as first_tak
            on first_tak.in_from_code = last_tak.in_from_code
           and first_tak.out_to_code = last_tak.out_to_code
        where !(first_tak.first_taking_cnt >=2 and last_tak.in_to_code is not null)
    ) base
    left join tmp as center
        on base.in_from_code = center.in_from_code
       and base.out_to_code = center.out_to_code 
    left join jms_tmp.dm_route_whole_shift_base_dt_mid as shift
        on center.in_from_code = shift.in_from_code
       and center.out_to_code = shift.out_to_code
       and center.in_to_code = shift.in_to_code
    group by base.in_from_code
            ,base.out_to_code
),

tmp_6 as (
    select 
         shift.*
        ,base.first_taking_cnt
    from (
        select
            last_tak.*
            ,first_tak.first_taking_cnt --最初时间段次数
        from tmp_4 as last_tak 
        left join tmp_2 as first_tak
            on first_tak.in_from_code = last_tak.in_from_code
           and first_tak.out_to_code = last_tak.out_to_code
        where last_tak.last_taking_cnt = first_tak.first_taking_cnt --最初=最终揽收时间段
        and first_tak.first_taking_cnt >= 2 --剔除单揽收时间段场景 --
    ) base
    left join tmp_3 as shift 
        on base.in_from_code = shift.in_from_code 
       and base.out_to_code = shift.out_to_code
       
    union all
    select 
         shift.*
        ,base.first_taking_cnt
    from (
        select
             base.*
            ,first_tak.first_taking_cnt
        from tmp_3 base
        left join tmp_4 as last_tak 
            on base.in_from_code = last_tak.in_from_code
           and base.out_to_code = last_tak.out_to_code
        left join tmp_2 as first_tak
            on base.in_from_code = first_tak.in_from_code
           and base.out_to_code = first_tak.out_to_code 
        where last_tak.last_taking_cnt <> first_tak.first_taking_cnt --最初<>最终揽收时间段
        and first_tak.first_taking_cnt >= 2 --剔除单揽收时间段场景 --
    ) base 
    left join (
        select *
        from (
            select *,row_number() over(partition by in_from_code,out_to_code,latest_warehousing_time_quantum order by search_type,total_time_use,num_of_transfer asc,branch_in_center_stop_time asc,in_to_code asc) as rk
            from jms_tmp.dm_route_whole_shift_base_dt_mid
        ) a where a.rk = 1
    ) center on base.in_from_code = center.in_from_code
            and base.out_to_code = center.out_to_code
    left join jms_tmp.dm_route_whole_shift_base_dt_mid as shift
        on center.in_from_code = shift.in_from_code
       and center.out_to_code = shift.out_to_code
       and center.latest_warehousing_time_quantum = shift.latest_warehousing_time_quantum
       and center.in_to_code = shift.in_to_code
       
    union all    
    select  
         shift.*
        ,base.first_taking_cnt
    from tmp_5 base 
    left join tmp_3 as shift
        on base.in_from_code = shift.in_from_code
       and base.out_to_code = shift.out_to_code
       and base.in_to_code = shift.in_to_code
    where base.if_center = 1 
    union all
    select 
         shift.in_from_code
        ,shift.update_date
        ,shift.in_from_provider_code
        ,shift.in_from_city_code
        ,shift.in_from_area_code
        ,shift.out_to_provider_code
        ,shift.out_to_city_code
        ,shift.out_to_area_code
        ,shift.out_to_code
        ,shift.in_branch_id
        ,shift.main_id
        ,shift.out_branch_id
        ,shift.in_collect_code
        ,shift.in_to_code
        ,shift.start_center_code
        ,shift.search_type
        ,shift.start_network_code
        ,shift.in_from_regional_code
        ,shift.in_from_regional_desc
        ,shift.in_from_financial_center_code
        ,shift.in_from_financial_center_desc
        ,shift.in_from_provider_desc
        ,shift.in_from_city_desc
        ,shift.in_from_area_desc
        ,shift.in_collect_name
        ,shift.in_from_name
        ,shift.in_to_name
        ,shift.in_edge_latest_warehousing
        ,shift.in_edge_planned_departure
        ,shift.in_edge_planned_arrival
        ,shift.in_edge2_planned_departure
        ,shift.in_edge2_planned_arrival
        ,shift.branch_in_span_days
        ,shift.branch_in_total_time
        ,shift.is_main_route
        ,shift.transfer_type
        ,shift.num_of_transfer
        ,shift.whole_route
        ,shift.e1_center_flow
        ,shift.e1_line_code
        ,shift.e1_planned_departure
        ,shift.e1_runtime
        ,shift.e1_planned_arrival
        ,shift.e1_e2_stop_time
        ,shift.e2_center_flow
        ,shift.e2_line_code
        ,shift.e2_planned_departure
        ,shift.e2_runtime
        ,shift.e2_planned_arrival
        ,shift.e2_e3_stop_time
        ,shift.e3_center_flow
        ,shift.e3_line_code
        ,shift.e3_planned_departure
        ,shift.e3_runtime
        ,shift.e3_planned_arrival
        ,shift.e3_e4_stop_time
        ,shift.e4_center_flow
        ,shift.e4_line_code
        ,shift.e4_planned_departure
        ,shift.e4_runtime
        ,shift.e4_planned_arrival
        ,shift.e4_e5_stop_time
        ,shift.e5_center_flow
        ,shift.e5_line_code
        ,shift.e5_planned_departure
        ,shift.e5_runtime
        ,shift.e5_planned_arrival
        ,shift.e5_e6_stop_time
        ,shift.e6_center_flow
        ,shift.e6_line_code
        ,shift.e6_planned_departure
        ,shift.e6_runtime
        ,shift.e6_planned_arrival
        ,shift.e6_e7_stop_time
        ,shift.e1_end_code
        ,shift.e2_end_code
        ,shift.e3_end_code
        ,shift.e4_end_code
        ,shift.e5_end_code
        ,shift.e6_end_code
        ,shift.transfer_total_span_days
        ,shift.transfer_total_time_use
        ,shift.main_total_span_days
        ,shift.main_total_time_use
        ,shift.end_center
        ,shift.end_center_code
        ,shift.start_center
        ,shift.out_from_code
        ,shift.out_collect_code
        ,shift.out_to_regional_desc
        ,shift.out_to_regional_code
        ,shift.out_to_financial_center_desc
        ,shift.out_to_financial_center_code
        ,shift.out_to_provider_desc
        ,shift.out_to_city_desc
        ,shift.out_to_area_desc
        ,shift.out_collect_name
        ,shift.out_to_name
        ,shift.out_from_name
        ,shift.out_has_collect
        ,shift.out_edge_planned_departure
        ,shift.out_edge_planned_arrival_time
        ,shift.out_edge2_planned_departure
        ,shift.out_edge2_planned_arrival_time
        ,shift.out_edge2_latest_warehouse_time
        ,shift.out_edge_latest_warehouse_time
        ,shift.out_edge_span_days_sign
        ,shift.out_edge2_span_days_sign
        ,shift.out_edge_deadline_sign_time
        ,shift.out_edge2_deadline_sign_time
        ,shift.branch_out_span_days
        ,shift.branch_out_total_time
        ,shift.effective_date
        ,shift.expiration_date
        ,shift.branch_in_center_stop_time
        ,shift.center_branch_out_stop_time
        ,shift.branch_in_center_span_days
        ,shift.center_branch_out_span_days
        ,shift.is_circuitous
        ,shift.out_edge_span_days_arrive
        ,shift.out_edge2_span_days_arrive
        ,shift.in_nodes
        ,shift.out_nodes
        ,shift.total_days_use
        ,shift.total_time_use
        ,shift.total_days_t
        ,shift.total_nodes
        ,shift.working_days
        ,shift.extra_in_collect_code
        ,shift.extra_in_collect_name
        ,shift.extra_in_line_name
        ,shift.extra_in_latest_warehousing
        ,shift.extra_in_planned_departure
        ,shift.extra_in_planned_arrival
        ,shift.extra_out_collect_code
        ,shift.extra_out_collect_name
        ,shift.extra_out_line_name
        ,shift.extra_out_latest_warehousing
        ,shift.extra_out_planned_departure
        ,shift.extra_out_planned_arrival
        ,shift.in_network_line_name
        ,shift.in_collect_line_name
        ,shift.out_network_line_name
        ,shift.out_collect_line_name
        ,shift.town_plus_time
        ,shift.branch_in_ship_time
        ,shift.branch_in_ship_span_days
        ,shift.branch_out_ship_time
        ,shift.branch_out_ship_span_days
        ,shift.all_line_name
        ,shift.out_town_plus_time
        ,shift.in_edge_run_time
        ,shift.in_edge_span_days
        ,shift.in_edge2_run_time
        ,shift.in_edge2_span_days
        ,shift.in_edge3_run_time
        ,shift.in_edge3_span_days
        ,shift.out_edge_run_time
        ,shift.out_edge_span_days
        ,shift.out_edge2_run_time
        ,shift.out_edge2_span_days
        ,shift.out_edge3_run_time
        ,shift.out_edge3_span_days
        ,shift.e1_span_days
        ,shift.e2_span_days
        ,shift.e3_span_days
        ,shift.e4_span_days
        ,shift.e5_span_days
        ,shift.e6_span_days
        ,shift.delivery_time
        ,shift.route_flow
        ,shift.in_edge2_latest_warehousing
        ,shift.e1_latest_warehousing
        ,shift.e2_latest_warehousing
        ,shift.e3_latest_warehousing
        ,shift.e4_latest_arrival_time
        ,shift.e5_latest_arrival_time
        ,shift.e6_latest_arrival_time
        ,shift.start_taking_shift
        ,shift.end_send_shift
        ,shift.warehouse_end_time
        ,shift.reserve_1
        ,shift.reserve_2
        ,shift.route_contain_main_line
        ,shift.direct_route_type
        ,shift.in_line_day
        ,shift.out_line_day
        ,shift.in_ship_span_days
        ,shift.in_manage_region_code
        ,shift.in_manage_region_name
        ,shift.out_manage_region_code
        ,shift.out_manage_region_name
        ,shift.latest_warehousing_time_quantum
        ,shift.branch_in_total_all_time
        ,shift.main_total_all_time
        ,shift.branch_out_total_all_time
        ,shift.lasted_line_name
        ,shift.if_city
        ,base.first_taking_cnt
    from tmp_5 base 
    left join tmp as shift 
        on base.in_from_code = shift.in_from_code
       and base.out_to_code = shift.out_to_code
    where base.if_center = 0 
),

--计算出最终班次
tmp_7 as (
    select *
    from (
        select
            *,row_number() over(partition by in_from_code,out_to_code,e1_line_code order by search_type,total_time_use,num_of_transfer asc,branch_in_center_stop_time asc) as rn 
        from tmp_6 
    ) a where a.rn = 1 
),

--再次计算出最终揽收时间段
tmp_8 as (
select
     in_from_code
    ,out_to_code
    ,count(distinct latest_warehousing_time_quantum) last_taking_cnt
from tmp_7 
group by in_from_code
        ,out_to_code
),

tmp_9 as (
    select
         a.in_from_code
        ,a.update_date
        ,a.in_from_provider_code
        ,a.in_from_city_code
        ,a.in_from_area_code
        ,a.out_to_provider_code
        ,a.out_to_city_code
        ,a.out_to_area_code
        ,a.out_to_code
        ,a.in_branch_id
        ,a.main_id
        ,a.out_branch_id
        ,a.in_collect_code
        ,a.in_to_code
        ,a.start_center_code
        ,a.search_type
        ,a.start_network_code
        ,a.in_from_regional_code
        ,a.in_from_regional_desc
        ,a.in_from_financial_center_code
        ,a.in_from_financial_center_desc
        ,a.in_from_provider_desc
        ,a.in_from_city_desc
        ,a.in_from_area_desc
        ,a.in_collect_name
        ,a.in_from_name
        ,a.in_to_name
        ,a.in_edge_latest_warehousing
        ,a.in_edge_planned_departure
        ,a.in_edge_planned_arrival
        ,a.in_edge2_planned_departure
        ,a.in_edge2_planned_arrival
        ,a.branch_in_span_days
        ,a.branch_in_total_time
        ,a.is_main_route
        ,a.transfer_type
        ,a.num_of_transfer
        ,a.whole_route
        ,a.e1_center_flow
        ,a.e1_line_code
        ,a.e1_planned_departure
        ,a.e1_runtime
        ,a.e1_planned_arrival
        ,a.e1_e2_stop_time
        ,a.e2_center_flow
        ,a.e2_line_code
        ,a.e2_planned_departure
        ,a.e2_runtime
        ,a.e2_planned_arrival
        ,a.e2_e3_stop_time
        ,a.e3_center_flow
        ,a.e3_line_code
        ,a.e3_planned_departure
        ,a.e3_runtime
        ,a.e3_planned_arrival
        ,a.e3_e4_stop_time
        ,a.e4_center_flow
        ,a.e4_line_code
        ,a.e4_planned_departure
        ,a.e4_runtime
        ,a.e4_planned_arrival
        ,a.e4_e5_stop_time
        ,a.e5_center_flow
        ,a.e5_line_code
        ,a.e5_planned_departure
        ,a.e5_runtime
        ,a.e5_planned_arrival
        ,a.e5_e6_stop_time
        ,a.e6_center_flow
        ,a.e6_line_code
        ,a.e6_planned_departure
        ,a.e6_runtime
        ,a.e6_planned_arrival
        ,a.e6_e7_stop_time
        ,a.e1_end_code
        ,a.e2_end_code
        ,a.e3_end_code
        ,a.e4_end_code
        ,a.e5_end_code
        ,a.e6_end_code
        ,a.transfer_total_span_days
        ,a.transfer_total_time_use
        ,a.main_total_span_days
        ,a.main_total_time_use
        ,a.end_center
        ,a.end_center_code
        ,a.start_center
        ,a.out_from_code
        ,a.out_collect_code
        ,a.out_to_regional_desc
        ,a.out_to_regional_code
        ,a.out_to_financial_center_desc
        ,a.out_to_financial_center_code
        ,a.out_to_provider_desc
        ,a.out_to_city_desc
        ,a.out_to_area_desc
        ,a.out_collect_name
        ,a.out_to_name
        ,a.out_from_name
        ,a.out_has_collect
        ,a.out_edge_planned_departure
        ,a.out_edge_planned_arrival_time
        ,a.out_edge2_planned_departure
        ,a.out_edge2_planned_arrival_time
        ,a.out_edge2_latest_warehouse_time
        ,a.out_edge_latest_warehouse_time
        ,a.out_edge_span_days_sign
        ,a.out_edge2_span_days_sign
        ,a.out_edge_deadline_sign_time
        ,a.out_edge2_deadline_sign_time
        ,a.branch_out_span_days
        ,a.branch_out_total_time
        ,a.effective_date
        ,a.expiration_date
        ,a.branch_in_center_stop_time
        ,a.center_branch_out_stop_time
        ,a.branch_in_center_span_days
        ,a.center_branch_out_span_days
        ,a.is_circuitous
        ,a.out_edge_span_days_arrive
        ,a.out_edge2_span_days_arrive
        ,a.in_nodes
        ,a.out_nodes
        ,a.total_days_use
        ,a.total_time_use
        ,a.total_days_t
        ,a.total_nodes
        ,a.working_days
        ,a.extra_in_collect_code
        ,a.extra_in_collect_name
        ,a.extra_in_line_name
        ,a.extra_in_latest_warehousing
        ,a.extra_in_planned_departure
        ,a.extra_in_planned_arrival
        ,a.extra_out_collect_code
        ,a.extra_out_collect_name
        ,a.extra_out_line_name
        ,a.extra_out_latest_warehousing
        ,a.extra_out_planned_departure
        ,a.extra_out_planned_arrival
        ,a.in_network_line_name
        ,a.in_collect_line_name
        ,a.out_network_line_name
        ,a.out_collect_line_name
        ,a.town_plus_time
        ,a.branch_in_ship_time
        ,a.branch_in_ship_span_days
        ,a.branch_out_ship_time
        ,a.branch_out_ship_span_days
        ,a.all_line_name
        ,a.out_town_plus_time
        ,a.in_edge_run_time
        ,a.in_edge_span_days
        ,a.in_edge2_run_time
        ,a.in_edge2_span_days
        ,a.in_edge3_run_time
        ,a.in_edge3_span_days
        ,a.out_edge_run_time
        ,a.out_edge_span_days
        ,a.out_edge2_run_time
        ,a.out_edge2_span_days
        ,a.out_edge3_run_time
        ,a.out_edge3_span_days
        ,a.e1_span_days
        ,a.e2_span_days
        ,a.e3_span_days
        ,a.e4_span_days
        ,a.e5_span_days
        ,a.e6_span_days
        ,a.delivery_time
        ,a.route_flow
        ,a.in_edge2_latest_warehousing
        ,a.e1_latest_warehousing
        ,a.e2_latest_warehousing
        ,a.e3_latest_warehousing
        ,a.e4_latest_arrival_time
        ,a.e5_latest_arrival_time
        ,a.e6_latest_arrival_time
        ,a.start_taking_shift
        ,a.end_send_shift
        ,a.warehouse_end_time
        ,a.reserve_1
        ,a.reserve_2
        ,a.route_contain_main_line
        ,a.direct_route_type
        ,a.in_line_day
        ,a.out_line_day
        ,a.in_ship_span_days
        ,a.in_manage_region_code
        ,a.in_manage_region_name
        ,a.out_manage_region_code
        ,a.out_manage_region_name
        ,a.latest_warehousing_time_quantum
        ,a.branch_in_total_all_time
        ,a.main_total_all_time
        ,a.branch_out_total_all_time
        ,a.lasted_line_name
        ,a.if_city
    from tmp_7 a
    left join tmp_8 b
      on a.in_from_code = b.in_from_code
     and a.out_to_code = b.out_to_code
    where a.first_taking_cnt = b.last_taking_cnt
    union all
    select
         b.in_from_code
        ,b.update_date
        ,b.in_from_provider_code
        ,b.in_from_city_code
        ,b.in_from_area_code
        ,b.out_to_provider_code
        ,b.out_to_city_code
        ,b.out_to_area_code
        ,b.out_to_code
        ,b.in_branch_id
        ,b.main_id
        ,b.out_branch_id
        ,b.in_collect_code
        ,b.in_to_code
        ,b.start_center_code
        ,b.search_type
        ,b.start_network_code
        ,b.in_from_regional_code
        ,b.in_from_regional_desc
        ,b.in_from_financial_center_code
        ,b.in_from_financial_center_desc
        ,b.in_from_provider_desc
        ,b.in_from_city_desc
        ,b.in_from_area_desc
        ,b.in_collect_name
        ,b.in_from_name
        ,b.in_to_name
        ,b.in_edge_latest_warehousing
        ,b.in_edge_planned_departure
        ,b.in_edge_planned_arrival
        ,b.in_edge2_planned_departure
        ,b.in_edge2_planned_arrival
        ,b.branch_in_span_days
        ,b.branch_in_total_time
        ,b.is_main_route
        ,b.transfer_type
        ,b.num_of_transfer
        ,b.whole_route
        ,b.e1_center_flow
        ,b.e1_line_code
        ,b.e1_planned_departure
        ,b.e1_runtime
        ,b.e1_planned_arrival
        ,b.e1_e2_stop_time
        ,b.e2_center_flow
        ,b.e2_line_code
        ,b.e2_planned_departure
        ,b.e2_runtime
        ,b.e2_planned_arrival
        ,b.e2_e3_stop_time
        ,b.e3_center_flow
        ,b.e3_line_code
        ,b.e3_planned_departure
        ,b.e3_runtime
        ,b.e3_planned_arrival
        ,b.e3_e4_stop_time
        ,b.e4_center_flow
        ,b.e4_line_code
        ,b.e4_planned_departure
        ,b.e4_runtime
        ,b.e4_planned_arrival
        ,b.e4_e5_stop_time
        ,b.e5_center_flow
        ,b.e5_line_code
        ,b.e5_planned_departure
        ,b.e5_runtime
        ,b.e5_planned_arrival
        ,b.e5_e6_stop_time
        ,b.e6_center_flow
        ,b.e6_line_code
        ,b.e6_planned_departure
        ,b.e6_runtime
        ,b.e6_planned_arrival
        ,b.e6_e7_stop_time
        ,b.e1_end_code
        ,b.e2_end_code
        ,b.e3_end_code
        ,b.e4_end_code
        ,b.e5_end_code
        ,b.e6_end_code
        ,b.transfer_total_span_days
        ,b.transfer_total_time_use
        ,b.main_total_span_days
        ,b.main_total_time_use
        ,b.end_center
        ,b.end_center_code
        ,b.start_center
        ,b.out_from_code
        ,b.out_collect_code
        ,b.out_to_regional_desc
        ,b.out_to_regional_code
        ,b.out_to_financial_center_desc
        ,b.out_to_financial_center_code
        ,b.out_to_provider_desc
        ,b.out_to_city_desc
        ,b.out_to_area_desc
        ,b.out_collect_name
        ,b.out_to_name
        ,b.out_from_name
        ,b.out_has_collect
        ,b.out_edge_planned_departure
        ,b.out_edge_planned_arrival_time
        ,b.out_edge2_planned_departure
        ,b.out_edge2_planned_arrival_time
        ,b.out_edge2_latest_warehouse_time
        ,b.out_edge_latest_warehouse_time
        ,b.out_edge_span_days_sign
        ,b.out_edge2_span_days_sign
        ,b.out_edge_deadline_sign_time
        ,b.out_edge2_deadline_sign_time
        ,b.branch_out_span_days
        ,b.branch_out_total_time
        ,b.effective_date
        ,b.expiration_date
        ,b.branch_in_center_stop_time
        ,b.center_branch_out_stop_time
        ,b.branch_in_center_span_days
        ,b.center_branch_out_span_days
        ,b.is_circuitous
        ,b.out_edge_span_days_arrive
        ,b.out_edge2_span_days_arrive
        ,b.in_nodes
        ,b.out_nodes
        ,b.total_days_use
        ,b.total_time_use
        ,b.total_days_t
        ,b.total_nodes
        ,b.working_days
        ,b.extra_in_collect_code
        ,b.extra_in_collect_name
        ,b.extra_in_line_name
        ,b.extra_in_latest_warehousing
        ,b.extra_in_planned_departure
        ,b.extra_in_planned_arrival
        ,b.extra_out_collect_code
        ,b.extra_out_collect_name
        ,b.extra_out_line_name
        ,b.extra_out_latest_warehousing
        ,b.extra_out_planned_departure
        ,b.extra_out_planned_arrival
        ,b.in_network_line_name
        ,b.in_collect_line_name
        ,b.out_network_line_name
        ,b.out_collect_line_name
        ,b.town_plus_time
        ,b.branch_in_ship_time
        ,b.branch_in_ship_span_days
        ,b.branch_out_ship_time
        ,b.branch_out_ship_span_days
        ,b.all_line_name
        ,b.out_town_plus_time
        ,b.in_edge_run_time
        ,b.in_edge_span_days
        ,b.in_edge2_run_time
        ,b.in_edge2_span_days
        ,b.in_edge3_run_time
        ,b.in_edge3_span_days
        ,b.out_edge_run_time
        ,b.out_edge_span_days
        ,b.out_edge2_run_time
        ,b.out_edge2_span_days
        ,b.out_edge3_run_time
        ,b.out_edge3_span_days
        ,b.e1_span_days
        ,b.e2_span_days
        ,b.e3_span_days
        ,b.e4_span_days
        ,b.e5_span_days
        ,b.e6_span_days
        ,b.delivery_time
        ,b.route_flow
        ,b.in_edge2_latest_warehousing
        ,b.e1_latest_warehousing
        ,b.e2_latest_warehousing
        ,b.e3_latest_warehousing
        ,b.e4_latest_arrival_time
        ,b.e5_latest_arrival_time
        ,b.e6_latest_arrival_time
        ,b.start_taking_shift
        ,b.end_send_shift
        ,b.warehouse_end_time
        ,b.reserve_1
        ,b.reserve_2
        ,b.route_contain_main_line
        ,b.direct_route_type
        ,b.in_line_day
        ,b.out_line_day
        ,b.in_ship_span_days
        ,b.in_manage_region_code
        ,b.in_manage_region_name
        ,b.out_manage_region_code
        ,b.out_manage_region_name
        ,b.latest_warehousing_time_quantum
        ,b.branch_in_total_all_time
        ,b.main_total_all_time
        ,b.branch_out_total_all_time
        ,b.lasted_line_name
        ,b.if_city
    from (
        select
             a.in_from_code 
            ,a.out_to_code
        from tmp_7 a
        left join tmp_8 b
          on a.in_from_code = b.in_from_code
         and a.out_to_code = b.out_to_code
        where a.first_taking_cnt <> b.last_taking_cnt
        group by a.in_from_code 
                ,a.out_to_code
    ) a 
    left join tmp_6 b
       on a.in_from_code = b.in_from_code
      and a.out_to_code = b.out_to_code
) 


insert overwrite table jms_dm.dm_route_whole_shift_base_dt
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum
    ,lasted_line_name
    ,platform_code_cn
    ,platform_cn
    ,effective_times_cn
    ,platform_code_zt
    ,platform_zt
    ,effective_times_zt
    ,platform_code_sf
    ,platform_sf
    ,effective_times_sf
    ,platform_code_yt
    ,platform_yt
    ,effective_times_yt
    ,platform_code_yd
    ,platform_yd
    ,effective_times_yd
    ,platform_code_thd
    ,platform_thd
    ,effective_times_thd
    ,platform_code_zjs
    ,platform_zjs
    ,effective_times_zjs
    ,case when if_equal = 1 and min_search_type <= effective_times_cn  then 1 else if(search_type <= effective_times_cn  ,1,0)  end as if_cn_cp
    ,case when if_equal = 1 and min_search_type <= effective_times_zt  then 1 else if(search_type <= effective_times_zt  ,1,0)  end as if_zt_cp
    ,case when if_equal = 1 and min_search_type <= effective_times_sf  then 1 else if(search_type <= effective_times_sf  ,1,0)  end as if_sf_cp
    ,case when if_equal = 1 and min_search_type <= effective_times_yt  then 1 else if(search_type <= effective_times_yt  ,1,0)  end as if_yt_cp
    ,case when if_equal = 1 and min_search_type <= effective_times_yd  then 1 else if(search_type <= effective_times_yd  ,1,0)  end as if_yd_cp
    ,case when if_equal = 1 and min_search_type <= effective_times_thd then 1 else if(search_type <= effective_times_thd ,1,0)  end as if_thd_cp
    ,case when if_equal = 1 and min_search_type <= effective_times_zjs then 1 else if(search_type <= effective_times_zjs ,1,0)  end as if_zjs_cp
    ,case when if_equal = 1 and min_search_type <= effective_times_cn  then 0 else if(search_type >  effective_times_cn  ,1,0)  end as if_cn_my
    ,case when if_equal = 1 and min_search_type <= effective_times_zt  then 0 else if(search_type >  effective_times_zt  ,1,0)  end as if_zt_my
    ,case when if_equal = 1 and min_search_type <= effective_times_sf  then 0 else if(search_type >  effective_times_sf  ,1,0)  end as if_sf_my
    ,case when if_equal = 1 and min_search_type <= effective_times_yt  then 0 else if(search_type >  effective_times_yt  ,1,0)  end as if_yt_my
    ,case when if_equal = 1 and min_search_type <= effective_times_yd  then 0 else if(search_type >  effective_times_yd  ,1,0)  end as if_yd_my
    ,case when if_equal = 1 and min_search_type <= effective_times_thd then 0 else if(search_type >  effective_times_thd ,1,0)  end as if_thd_my
    ,case when if_equal = 1 and min_search_type <= effective_times_zjs then 0 else if(search_type >  effective_times_zjs ,1,0)  end as if_zjs_my
    ,if_city as if_city --同城件
    ,hz_cnt  as hz_cnt  --核载票数
    ,null as branch_shifts --集货班次 1集/2集
    ,branch_in_total_all_time   --集货总时效
    ,main_total_all_time        --干线总时效
    ,branch_out_total_all_time  --散货总时效
    ,date_add('{{ execution_date | cst_ds }}',1) as dt
from (
    select
         route.*
        ,cn_effe.platform_code                  as platform_code_cn
        ,cn_effe.platform                       as platform_cn
        ,cn_effe.times                          as effective_times_cn
        ,zt_effe.platform_code                  as platform_code_zt
        ,zt_effe.platform                       as platform_zt
        ,zt_effe.times                          as effective_times_zt
        ,sf_effe.platform_code                  as platform_code_sf
        ,sf_effe.platform                       as platform_sf
        ,sf_effe.times                          as effective_times_sf
        ,yt_effe.platform_code                  as platform_code_yt
        ,yt_effe.platform                       as platform_yt
        ,yt_effe.times                          as effective_times_yt
        ,yd_effe.platform_code                  as platform_code_yd
        ,yd_effe.platform                       as platform_yd
        ,yd_effe.times                          as effective_times_yd
        ,thd_effe.platform_code                 as platform_code_thd
        ,thd_effe.platform                      as platform_thd
        ,thd_effe.times                         as effective_times_thd
        ,zjs_effe.platform_code                 as platform_code_zjs
        ,zjs_effe.platform                      as platform_zjs
        ,zjs_effe.times                         as effective_times_zjs
        ,min(route.search_type) over(partition by route.in_from_code,route.out_to_code,route.latest_warehousing_time_quantum) as min_search_type --最小的时效
        ,max(route.if_city) over(partition by route.in_from_code,route.out_to_code,route.latest_warehousing_time_quantum) as if_equal --同城件
        ,hz.hz_cnt as hz_cnt --核载票数
    from tmp_9 as route
    left join (
        select
            id
           ,code
           ,name
           ,start_code as start_center_code
           ,start_name as start_center_name
           ,vehicle_typegroup --车型
        from jms_dim.dim_yl_tmsnew_tms_vehicle_line_base_dt --车线表
        where is_enable = 1
        and dt = '{{ execution_date | cst_ds }}'
    ) vehicle on nvl(replace(route.e1_line_code,'(计重)',''),route.in_from_code) = vehicle.name
    left join (
        select
            distributecode as center_code
           ,distributename as center_name
           ,vehicletype    as hz_type  --核载车型
           ,vehiclecount   as hz_cnt   --核载票数
        from jms_dim.dim_tms_mainline_vehicleloading --干线核载维护表
        where distributecode is not null
    ) hz on vehicle.start_center_code = hz.center_code --用班次的始发 和 车型 去匹配
        and vehicle.vehicle_typegroup = hz.hz_type
    left join(
         select *
         from jms_dim.dim_tab_rou_competitor_effective --同行时效配置表
         where platform_code = 1 --菜鸟
    ) cn_effe on cn_effe.sender_city_id = route.in_from_city_code
             and cn_effe.receiver_city_id = route.out_to_city_code
    left join(
        select *
        from jms_dim.dim_tab_rou_competitor_effective
        where platform_code = 2 --中通
    ) zt_effe on zt_effe.sender_city_id = route.in_from_city_code
             and zt_effe.receiver_city_id = route.out_to_city_code
    left join(
        select *
        from jms_dim.dim_tab_rou_competitor_effective
        where platform_code = 3 --顺丰
    ) sf_effe on sf_effe.sender_city_id = route.in_from_city_code
             and sf_effe.receiver_city_id = route.out_to_city_code
    left join(
        select *
        from jms_dim.dim_tab_rou_competitor_effective
        where platform_code = 4 --圆通
    ) yt_effe on yt_effe.sender_city_id = route.in_from_city_code
             and yt_effe.receiver_city_id = route.out_to_city_code
    left join(
        select *
        from jms_dim.dim_tab_rou_competitor_effective
        where platform_code = 5 --韵达
    ) yd_effe on yd_effe.sender_city_id = route.in_from_city_code
             and yd_effe.receiver_city_id = route.out_to_city_code
    left join(
        select *
        from jms_dim.dim_tab_rou_competitor_effective
        where platform_code = 6 --桃花岛
    ) thd_effe on thd_effe.sender_city_id = route.in_from_city_code
              and thd_effe.receiver_city_id = route.out_to_city_code
    left join(
        select *
        from jms_dim.dim_tab_rou_competitor_effective
        where platform_code = 7 --紫金山
    ) zjs_effe on zjs_effe.sender_city_id = route.in_from_city_code
              and zjs_effe.receiver_city_id = route.out_to_city_code
) a
distribute by pmod(hash(rand()),200);

